/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package east2west.dao;

import east2west.dto.TourDTO;
import east2west.dto.TourPriceDTO;
import east2west.interfaces.TourPriceInterface;
import east2west.util.DatabaseUtil;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import org.apache.jasper.tagplugins.jstl.core.ForEach;

/**
 *
 * @author thanhluan
 */
public class TourPriceDAO implements TourPriceInterface {

    Connection con = null;
    CallableStatement call = null;
    ResultSet rs = null;

    public ArrayList<TourPriceDTO> readByAll() {
        try {
            con = DatabaseUtil.getConnection();

            call = con.prepareCall("{call sp_Call_View_TourPrice}");
            rs = call.executeQuery();
            ArrayList<TourPriceDTO> list = new ArrayList<TourPriceDTO>();
            while (rs.next()) {
                TourPriceDTO dto = new TourPriceDTO();

                dto.setTourPriceId(rs.getInt(1));
                dto.setTourId(rs.getInt(2));
                dto.setQuantity(rs.getInt(3));
                dto.setPrice(rs.getFloat(4));
                dto.setChildPrice(rs.getFloat(5));
                list.add(dto);
            }
            return list;
        } catch (SQLException ex) {
            return null;
        }
    }

    public ArrayList<TourPriceDTO> findPrice(float price) {
        try {
            con = DatabaseUtil.getConnection();

            call = con.prepareCall("{call sp_Call_View_TourPrice_FindPrice(?)}");
            call.setFloat(1, price);
            rs = call.executeQuery();
            ArrayList<TourPriceDTO> list = new ArrayList<TourPriceDTO>();
            while (rs.next()) {
                TourPriceDTO dto = new TourPriceDTO();

                dto.setTourPriceId(rs.getInt(1));
                dto.setTourId(rs.getInt(2));
                dto.setQuantity(rs.getInt(3));
                dto.setPrice(rs.getFloat(4));
                dto.setChildPrice(rs.getFloat(5));
                list.add(dto);
            }
            return list;
        } catch (SQLException ex) {
            return null;
        }
    }

    public ArrayList<TourPriceDTO> findByDto(TourPriceDTO dto) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public TourPriceDTO findById(int id) {
        try {
            con = DatabaseUtil.getConnection();

            call = con.prepareCall("{call sp_Call_View_TourPrice_FindId(?)}");
            call.setInt(1, id);
            rs = call.executeQuery();
            TourPriceDTO dto = null;
            while (rs.next()) {
                dto = new TourPriceDTO();
                dto.setTourPriceId(rs.getInt(1));
                dto.setTourId(rs.getInt(2));
                dto.setQuantity(rs.getInt(3));
                dto.setPrice(rs.getFloat(4));

            }
            return dto;
        } catch (SQLException ex) {
            return null;
        }
    }

    public boolean create(TourPriceDTO dto) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Insert_TourPrice(?,?,?)}");
            call.setInt(1, dto.getTourId());
            call.setInt(2, dto.getQuantity());
            call.setFloat(3, dto.getPrice());
            call.executeUpdate();
            return true;
        } catch (SQLException ex) {
            return false;

        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DatabaseUtil.closeConnection(con);
                }
            } catch (SQLException ex) {
            }
        }
    }

    public boolean update(TourPriceDTO dto) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Update_TourPrice(?,?)}");
            call.setInt(1, dto.getTourPriceId());
            call.setFloat(2, dto.getPrice());
            call.executeUpdate();
            return true;
        } catch (SQLException ex) {
            return false;

        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DatabaseUtil.closeConnection(con);
                }
            } catch (SQLException ex) {
            }
        }
    }

    public boolean delete(TourPriceDTO dto) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public boolean delete(int id) {
        try {
            con = DatabaseUtil.getConnection();
            con.setAutoCommit(false);
            call = con.prepareCall("{call sp_Insert_TourPrice(?)}");
            call.setInt(1, id);
            call.executeUpdate();
            return true;
        } catch (Exception ex) {
            if (con != null) {
                try {
                    con.rollback();
                } catch (SQLException ex1) {
                }
            }
            return false;
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DatabaseUtil.closeConnection(con);
                }
            } catch (SQLException ex) {
            }
        }

    }

    public float getMin(int id) {
        float min = 0;
        try {
            con = DatabaseUtil.getConnection();

            call = con.prepareCall("SELECT * FROM V_MinPrice WHERE TourId = ?");
            call.setInt(1, id);
            rs = call.executeQuery();
            TourPriceDTO dto = null;
            while (rs.next()) {
                dto = new TourPriceDTO();

                min = rs.getFloat("MinPrice");


            }
            return min;
        } catch (SQLException ex) {
            return 0;
        }
    }

    public float getMax(int id) {
        float max = 0;
        try {
            con = DatabaseUtil.getConnection();

            call = con.prepareCall("SELECT * FROM V_MaxPrice WHERE TourId = ?");
            call.setInt(1, id);
            rs = call.executeQuery();
            while (rs.next()) {
                max = rs.getFloat("MaxPrice");

            }
            return max;
        } catch (SQLException ex) {
            return 0;
        }
    }

    public ArrayList<TourPriceDTO> avgPrice(int id) {
        try {
            con = DatabaseUtil.getConnection();

            call = con.prepareCall("SELECT * FROM V_TourPrice WHERE TourId = ?");
            call.setInt(1, id);
            rs = call.executeQuery();
            ArrayList<TourPriceDTO> avgListPrice = new ArrayList<TourPriceDTO>();
            TourPriceDTO dto = null;
            while (rs.next()) {
                dto = new TourPriceDTO();
                dto.setTourPriceId(rs.getInt(1));
                dto.setTourId(rs.getInt(2));
                dto.setQuantity(rs.getInt(3));
                dto.setPrice(rs.getFloat(4));
                avgListPrice.add(dto);

            }
            return avgListPrice;
        } catch (SQLException ex) {
            return null;
        }
    }
     public TourPriceDTO getPirceIdTop(int id) {
        try {
            con = DatabaseUtil.getConnection();

            call = con.prepareCall("SELECT Top(1) * FROM V_TourPrice WHERE TourId = ?");
            call.setInt(1, id);
            rs = call.executeQuery();
           // ArrayList<TourPriceDTO> avgListPrice = new ArrayList<TourPriceDTO>();
            TourPriceDTO dto = null;
            while (rs.next()) {
                dto = new TourPriceDTO();
                dto.setTourPriceId(rs.getInt(1));
                dto.setTourId(rs.getInt(2));
                dto.setQuantity(rs.getInt(3));
                dto.setPrice(rs.getFloat(4));

            }
            return dto;
        } catch (SQLException ex) {
            return null;
        }
    }
}
